package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import beans.Group;
import beans.User;

public class GroupsDB extends DB
{

	/*   
   	 ************************************************************************
	 AUTHOR     : KEITH WESTPHAL
	 			: MYLES DALE
	 			: COREY STONE
     METHOD     : SEARCH VIEW
     PROCESS    : GETS USERS THAT ARE IN WHICH GROUPS 
     ************************************************************************
*/
	
	public static synchronized ArrayList<Group> getGroups(int group_id, int userid)
	{
		Connection conn = null;
		ArrayList<Group> groupli = new ArrayList<Group>();
		PreparedStatement statement = null;
		String sql = "SELECT gu.group_id, gu.userid, gua.group_name\n" +
					 "FROM group_user gu\n" +
					 "JOIN group_uafs gua ON (gu.group_id = gua.group_id)\n" +
					 "WHERE gu.group_id = (?) OR \n" +
					 "gu.userid = (?)";

		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, group_id);
			statement.setInt(2, userid);
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				Group group = new Group();
				group.setGroup_id(rs.getInt(1));
				group.setUser_id(rs.getInt(2));
				group.setGroup_name(rs.getString(3));
				
				groupli.add(group);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return groupli;
	}
	
	/*   
   	 ************************************************************************
	 AUTHOR     : KEITH WESTPHAL
	 			: MYLES DALE
	 			: COREY STONE
     METHOD     : SEARCH VIEW
     PROCESS    : GETS GROUPINFO FROM GROUP TABLE
     ************************************************************************
*/
	
	public static synchronized ArrayList<Group> getGroupInfo(int group_id)
	{
		Connection conn = null;
		ArrayList<Group> groupli = new ArrayList<Group>();
		PreparedStatement statement = null;
		String sql = "SELECT group_id, group_name, group_descrip, isadmin, isAccepted\n" + // to be altered for new database tables
			  		 "FROM group_uafs\n" +
			  		 "WHERE group_id = (?)";      // groups table contains all info on a group

		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, group_id);
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				Group group = new Group();
				group.setGroup_id(rs.getInt(1));
				group.setGroup_name(rs.getString(2));
				
				groupli.add(group);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return groupli;
	}
	
	/*   
   	 ************************************************************************
	 AUTHOR     : KEITH WESTPHAL
	 			: MYLES DALE
	 			: COREY STONE
     METHOD     : addgroup(groupbean group)
     PROCESS    : ADDS A GROUP 
     ************************************************************************
*/
	
	public static synchronized int addGroup(Group group)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "INSERT INTO group_uafs(group_name, group_descrip)\n" +
					 "VALUES(?, ?)";//This may need some modification but technically, it creates a group
		String sql2 = "INSERT INTO group_user(group_id, userid)\n" +
					 "VALUES((SELECT max(group_id) FROM group_uafs), ?)";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setString(1, group.getGroup_name());
			statement.setString(2, group.getGroup_descrip());
			
			status = statement.executeUpdate();
			
			statement = conn.prepareStatement(sql2);
			statement.setInt(1, group.getUser_id());
			status = statement.executeUpdate();
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
	
	/*   
   	 ************************************************************************
	 AUTHOR     : KEITH WESTPHAL
	 			: MYLES DALE
     METHOD     : addgroup(groupbean group)
     PROCESS    : GETS THE LINK INFORMATION BETWEEN GROUPS AND USERS 
                : ALLOWS FOR LINKS TO GO TO GROUPS AND USERS
     ************************************************************************
*/
	
	public static synchronized int addGroupUser(Group group)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "INSERT INTO group_user(group_ID, userID)\n" +
					 "VALUES(?, ?)";
		
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, group.getGroup_id());
			statement.setInt(2, group.getUser_id());
			
			status = statement.executeUpdate();
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
	
	//*************************************************************************
	// Author   : Corey Stone
	// Abstract : Get Group name individually to pass to jsp pages
	//
	//*************************************************************************
	public static synchronized  ArrayList<Group> getGroupName(int group_id, int user_id)
	{
		Connection conn = null;
		ArrayList<Group> groupli = new ArrayList<Group>();
		PreparedStatement statement = null;
		String sql = "SELECT group_name, group_descrip\n" + // to be altered for new database tables
			  		 "FROM group_uafs\n" +
			  		 "WHERE group_id = (?)";      // groups table contains all info on a group

		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, group_id);
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				Group group = new Group();
				group.setGroup_name(rs.getString(1));
				group.setGroup_descrip(rs.getString(2));
				
				groupli.add(group);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return groupli;
		
		
	}
	
}
